package GestionPlannification;

import GestionBilleterie.Reservation;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Classe permettant d'accéder aux reservations de la base de données
 * @author Julien Reze
 */
class RepresentationBD {
    /**
     * Retourne le nombre de reservations de la representation pour un tarif
     * @param idTarif l'id du tarif recherché
     * @param idRepresentation l'id de la representation
     * @return le nombre de reservations
     * @throws java.sql.SQLException
     */
    public static int getNbReservation(int idTarif,int idRepresentation) throws SQLException{
        int result;
        Statement st = null;
        st=BD.UserBD.getStatement();
        String query;
        query="select count(r.rese_id) as NbPlacesDispo "+
            "from tarif t, reservation r "+
            "where (t.tari_id=r.tari_id and t.tari_id="+idTarif+" and r.repr_id="+idRepresentation+")";
        ResultSet rs= st.executeQuery(query);
        rs.next();
        result=rs.getInt("NbPlacesDispo");
        BD.UserBD.close(st);
        return result;
    }

    /**
     * Retourne le nombre de places disponilbes de la representation pour un tarif
     * @param idTarif l'id du tarif recherché
     * @param idRepresentation l'id de la representation
     * @return le nombre de places disponilbes
     * @throws java.sql.SQLException
     */
    public static int getNbPlacesDisponibles(int idTarif,int idRepresentation) throws SQLException{
        int result;
        Statement st = null;
        st=BD.UserBD.getStatement();
        String query;
        query="select (t.tari_nbPlace-count(r.rese_id)) as NbPlacesDispo "+
            "from tarif t, reservation r "+
            "where (t.tari_id=r.tari_id and t.tari_id="+idTarif+" and r.repr_id="+idRepresentation+")";
        ResultSet rs= st.executeQuery(query);
        rs.next();
        result=rs.getInt("NbPlacesDispo");
        BD.UserBD.close(st);
        return result;
    }

    /**
     * Recherche une representation dans la base de données grâce à son id
     * @param idRepresentation l'id de la representation recherchée
     * @return la representation trouvée
     * @throws java.sql.SQLException
     * @throws java.lang.Exception
     */
    static Representation readRepresentation(int idRepresentation) throws SQLException, Exception {
        Statement st = null;
        st=BD.UserBD.getStatement();
        ResultSet rs= st.executeQuery("SELECT * FROM representation WHERE repr_id="+idRepresentation+"");
        rs.next();
        Representation r=new Representation(idRepresentation, rs.getDate("repr_date"), Piece.findEntitie(rs.getInt("piec_id")));
        r.addRerservations(Reservation.searchReservations(r));
        BD.UserBD.close(st);
        return r;
    }

    /**
     * Retourne toutes les representations de la base de données pour une pièce
     * @param p la pièce recherchée
     * @return la liste des representations trouvées
     * @throws java.sql.SQLException
     * @throws java.lang.Exception
     */
    static List<Representation> readAllRepresentations(Piece p) throws SQLException, Exception {
        Statement st = null;
        List<Representation> lesRepresentations=new ArrayList();
        st=BD.UserBD.getStatement();
        ResultSet rs= st.executeQuery("SELECT * FROM representation where piec_id="+p.getId()+" ORDER BY repr_date");
        while( rs.next( )){
            Representation r=new Representation(rs.getInt("repr_id"),rs.getDate("repr_date"), p);
            r.addRerservations(Reservation.searchReservations(r));
            lesRepresentations.add(r);
        }
        BD.UserBD.close(st);
        return lesRepresentations;
    }

    /**
     * Retourne toutes les representations de la base de données
     * @return la liste des representations
     * @throws java.sql.SQLException
     */
    static List<Representation> readAllRepresentations() throws SQLException, Exception {
        Statement st = null;
        List<Representation> lesRepresentations=new ArrayList();
        st=BD.UserBD.getStatement();
        ResultSet rs= st.executeQuery("SELECT * FROM representation");
        while( rs.next( )){
            Representation r=new Representation(rs.getInt("repr_id"),rs.getDate("repr_date"), Piece.findEntitie(rs.getInt("piec_id")));
            r.addRerservations(Reservation.searchReservations(r));
            lesRepresentations.add(r);
        }
        BD.UserBD.close(st);
        return lesRepresentations;
    }

}
